﻿Imports Npgsql

Public Class Servicos
    Private comando As New NpgsqlCommand()
    Private bd As New ConexaoBd()
    Private tblAnimal As DataTable
    Private tblValor As DataTable
    Private tRow As DataRow
    Private dset As DataSet
    Private dsetClientes As DataSet
    Private dsetAnimais As DataSet
    Private dsetServicos As DataSet
    Private limpar As New LimparDados()
    Private load As Boolean = False


    Private Sub servicos_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

        'Combo Cliente
        Dim sqlClientes As String = "SELECT idcliente, nome FROM clientes UNION SELECT NULL, NULL"
        dsetClientes = bd.carregarDados(sqlClientes, "clientes")
        cboCliente.DataSource = dsetClientes.Tables("clientes").DefaultView
        cboCliente.DisplayMember = "nome"
        cboCliente.ValueMember = "idcliente"
        cboCliente.Sorted = False

        'Combo Servicos


        Dim sqlServicos As String = "SELECT idtpservico, nome FROM tp_servicos UNION SELECT NULL, NULL"
        dsetServicos = bd.carregarDados(sqlServicos, "servicos")
        cboServico.DataSource = dsetServicos.Tables("servicos").DefaultView
        cboServico.DisplayMember = "nome"
        cboServico.ValueMember = "idtpservico"
        cboServico.Sorted = False

        load = True
    End Sub


    Private Sub btnSalvar_Click(sender As System.Object, e As System.EventArgs) Handles btnSalvar.Click

        Try
            If Val(txtCod.Text.ToString()) > 0 Then
                comando.CommandText = "UPDATE servicos SET idtpservico=@idtpservico, idcliente=@idcliente, idanimal=@idanimal, valor=@valor, dataservico=DEFAULT WHERE idservico=@idservico;"

                comando.Parameters.Add("@idtpservico", cboServico.SelectedValue)
                comando.Parameters.Add("@idcliente", cboCliente.SelectedValue)
                comando.Parameters.Add("@idanimal", cboAnimal.SelectedValue)
                comando.Parameters.Add("@valor", mtxtValor.Text)
                comando.Parameters.Add("@idservico", txtCod.Text)


                bd.atualizarDados(comando)

            Else
                comando.CommandText = "INSERT INTO servicos VALUES (DEFAULT, @idtpservico, @idcliente, @idanimal, @valor, DEFAULT);"

                comando.Parameters.Add("@idtpservico", cboServico.SelectedValue)
                comando.Parameters.Add("@idcliente", cboCliente.SelectedValue)
                comando.Parameters.Add("@idanimal", cboAnimal.SelectedValue)
                comando.Parameters.Add("@valor", mtxtValor.Text)


                bd.adicionarDados(comando)


                Dim sql As String = "SELECT MAX(idservico) id FROM servicos"

                'Realizando consulta
                dset = bd.carregarDados(sql, "idservico")
                tblAnimal = dset.Tables.Item(0)
                dset.Dispose()
                tRow = tblAnimal.Rows(0)
                Dim id As Integer
                id = CInt(tRow("id").ToString())
                carregarServico(id)

            End If

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub

    Private Sub carregarServico(ByVal id As Integer)
        Dim sql As String = "SELECT * FROM servicos WHERE idservico = " + id.ToString()

        'Realizando consulta
        dset = bd.carregarDados(sql, "servico")
        tblAnimal = dset.Tables.Item(0)
        dset.Dispose()

        If tblAnimal.Rows.Count() = 1 Then
            tRow = tblAnimal.Rows(0)

            txtCod.Text = tRow("idservico").ToString()
            cboCliente.SelectedValue = tRow("idcliente").ToString()
            carregarAnimal(Val(tRow("idcliente").ToString()))
            cboAnimal.SelectedValue = tRow("idanimal").ToString()
            cboServico.SelectedValue = tRow("idtpservico").ToString()
            mtxtValor.Text = tRow("valor").ToString()

            btnExcluir.Enabled = True
        Else
            MessageBox.Show("Serviço Não Encontrado!")
            btnExcluir.Enabled = False
            limpar.Limpar(Me)
        End If


    End Sub

    Private Sub txtCod_Leave(sender As System.Object, e As System.EventArgs) Handles txtCod.Leave
        If txtCod.Text.Length > 0 And Val(txtCod.Text) > 0 Then
            carregarServico(Val(txtCod.Text))
        End If
    End Sub

    Private Sub txtCod_KeyDown(sender As System.Object, e As System.Windows.Forms.KeyEventArgs) Handles txtCod.KeyDown
        If e.KeyCode = 13 Then
            cboCliente.Focus()
        End If
    End Sub

    Private Sub btnPesquisar_Click(sender As System.Object, e As System.EventArgs) Handles btnPesquisar.Click

        Dim sql As String = "SELECT s.idservico, c.nome cliente, a.nome animal, s.dataservico FROM servicos s INNER JOIN clientes c ON s.idcliente = c.idcliente INNER JOIN animais a ON s.idanimal = a.idanimal INNER JOIN tp_servicos ts ON s.idtpservico = ts.idtpservico"
        dset = bd.carregarDados(sql, "animais")

        grdAnimal.DataSource = dset.Tables("animais").DefaultView


        grdAnimal.Columns(0).HeaderText = "Código"
        grdAnimal.Columns(1).HeaderText = "Cliente"
        grdAnimal.Columns(2).HeaderText = "Animal"
        grdAnimal.Columns(3).HeaderText = "Data Serv"
    End Sub

    Private Sub grdAnimal_RowHeaderMouseDoubleClick(sender As System.Object, e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles grdAnimal.RowHeaderMouseDoubleClick
        carregarServico(grdAnimal.CurrentRow.Cells(0).Value)
        TabControl1.SelectTab(0)
        cboCliente.Focus()

    End Sub

    Private Sub btnNovo_Click(sender As System.Object, e As System.EventArgs) Handles btnNovo.Click
        limpar.Limpar(Me)
        cboCliente.Focus()
    End Sub

    Private Sub btnExcluir_Click(sender As System.Object, e As System.EventArgs) Handles btnExcluir.Click
        comando.CommandText = "DELETE FROM servicos WHERE idservico = @idservico"
        comando.Parameters.Add("@idservico", txtCod.Text)
        bd.atualizarDados(comando)
        limpar.Limpar(Me)
    End Sub

    Private Sub cboCliente_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cboCliente.SelectedIndexChanged
        If Not load Then
            Return
        End If

        'Combo Animais
        If Val(cboCliente.SelectedValue.ToString()) > 0 Then
            carregarAnimal(Val(cboCliente.SelectedValue.ToString()))
        End If

    End Sub

    Private Sub carregarAnimal(ByVal idCliente As Integer)
        Dim sqlAnimais As String = "SELECT idanimal, nome FROM animais WHERE idcliente = " + idCliente.ToString() + " UNION SELECT NULL, NULL"
        dsetAnimais = bd.carregarDados(sqlAnimais, "animais")
        cboAnimal.DataSource = dsetAnimais.Tables("animais").DefaultView
        cboAnimal.DisplayMember = "nome"
        cboAnimal.ValueMember = "idanimal"
        cboAnimal.Sorted = False
    End Sub

    Private Sub cboServico_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cboServico.SelectedIndexChanged
        If Not load Then
            Return
        End If

        'Valor
        If Val(cboServico.SelectedValue.ToString()) > 0 Then
            Dim sql As String = "SELECT valor FROM tp_servicos WHERE idtpservico = " + cboServico.SelectedValue.ToString()
            dset = bd.carregarDados(sql, "valor")
            tblValor = dset.Tables.Item(0)
            dset.Dispose()
            tRow = tblValor.Rows(0)
            mtxtValor.Text = tRow("valor").ToString()
        End If

    End Sub
End Class